package com.fpno3.blog;

import com.fpno.bean.Customer;
import com.fpno.util.JDBCUtils;
import org.junit.Test;

import java.io.*;
import java.sql.*;

/*
* preparedStatement操作blog
* */
public class BlogTest {

    //向数据表customers中插入blog类型数据
    @Test
    public void testInsert()throws Exception{
        Connection connection = JDBCUtils.getConnection();
        String sql="insert into customers(name,email,birth,photo)values (?,?,?,?)";
        PreparedStatement ps = connection.prepareStatement(sql);

        ps.setObject(1,"张宇浩");
        ps.setObject(2,"zhang@qq.com");
        ps.setObject(3,"1992-1-1");
        FileInputStream is = new FileInputStream(new File("back.jpg"));
        ps.setBlob(4,is);
        ps.execute();
        JDBCUtils.closeResource(connection,ps);
    }

    //查询数据表中的blog数据
    @Test
    public void testQuery()throws Exception{
        Connection connection = JDBCUtils.getConnection();
        String sql="select id,name,email,birth,photo from customers where id=?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1,21);
        ResultSet resultSet = ps.executeQuery();
        if (resultSet.next()){
            //方式一
            /*int id = resultSet.getInt(1);
            String name = resultSet.getString(2);
            String email = resultSet.getString(3);
            Date birth = resultSet.getDate(4);*/

            //方式二
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            String email = resultSet.getString("email");
            Date birth = resultSet.getDate("birth");
            Customer customer = new Customer(id, name, email, birth);
            System.out.println(customer);

            Blob photo = resultSet.getBlob("photo");
            InputStream is = photo.getBinaryStream();
            FileOutputStream fos = new FileOutputStream("zhangyuhao.jpg");
            byte[] buffer = new byte[1024];
            int len;
            while ((len=is.read(buffer))!=-1){
                fos.write(buffer,0,len);
            }
            is.close();
            fos.close();
        }

        JDBCUtils.closeResource(connection,ps,resultSet);
    }
}
